import traceback
import pyautogui
import os
import time
import pyperclip as clip
import json
import pandas as pd
from datetime import datetime
from sqlalchemy import create_engine
import pypinyin
from bs4 import BeautifulSoup
import random
from jsjj.util.configUtil import *
from jsjj.email.sendEmail import *
from jsjj.util.dbUtil import *
from jsjj.util.emailUtil import *
from jsjj.util.logUtil import *
from jsjj.util.timeUtil import *
from jsjj.util.dirUtil import *
from jsonpath import jsonpath
from jsjj.util.mouseMoveUtil import *
import math
import uuid
import numpy as np

runMode = '日模式'  # 只有日模式，返回数据中，完全是没有任何日期信息的，因此没有周、月模式
# 拼多多-商家后台-数据中心-服务数据-领航员
modeName = '拼多多_商家后台_商品列表_线上商品'

downloadFilePath = getdownloadFilePath()
globalUUID = str(uuid.uuid1())


# 左边点击，直到点击到一级类目，该过程不参与循环
def SPGK(engine, conn):
    # 关闭电霸每天晚上会弹出的一个屏蔽页面
    pyautogui.FAILSAFE = False

    sleep(6)
    pyautogui.scroll(10000)

    # 左边--【物流工具】
    viaCommonUrl('https://mms.pinduoduo.com/orders/order/carriage/list')

    sleep(4)
    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')

    jsonFromWebShopName = getSjhtShopName()

    sleep(2)
    # 左边--【商品列表】/默认就是在售中、降序排列
    viaCommonUrl('https://mms.pinduoduo.com/goods/goods_list')

    # *********************************************切换为100条数据每页*********************************************
    # clear network
    pyautogui.moveTo(1540, 144, 4)
    pyautogui.click()

    pyautogui.moveTo(1177, 450, 4)

    sleep(4)
    pyautogui.scroll(-10000)
    sleep(2)

    # 点击翻页下拉框
    pyautogui.moveTo(1021, 570, 4)
    pyautogui.click()

    # 选择每页100条
    pyautogui.moveTo(1005, 685, 1)
    pyautogui.click()

    sleep(2)
    pyautogui.scroll(-10000)
    sleep(2)

    # *********************************************切换为100条数据每页*********************************************

    sleep(6)    #这里查询100，页面可能卡主，因此需要等待
    # 保存第一页的在售商品信息
    # filterbox
    pyautogui.moveTo(1513, 169, 4)
    pyautogui.doubleClick()

    sleep(2)
    pyautogui.hotkey('ctrl', 'a')

    sleep(2)
    clip.copy('goodsList')  # 先复制
    pyautogui.hotkey('ctrl', 'v')

    # 选中第一个文件
    pyautogui.moveTo(1518, 325, 2)
    pyautogui.click()

    # 选json区域
    pyautogui.moveTo(1823, 416, 4)
    pyautogui.click()

    sleep(2)
    pyautogui.hotkey('ctrl', 'a')

    sleep(2)
    pyautogui.hotkey('ctrl', 'c')

    # 读取剪切板内容json内容
    sleep(2)
    value = clip.paste()
    firstPageJson = json.loads(value)

    # jsonDf = {"quantity": jsonpath(firstPageJson, "$..result..goods_list..quantity"),
    #           "score": jsonpath(firstPageJson, "$..result..goods_list..score"),
    #           "resource": jsonpath(firstPageJson, "$..result..goods_list..resource"),
    #           "priority": jsonpath(firstPageJson, "$..result..goods_list..priority"),
    #           "sku_group_price1": jsonpath(firstPageJson, "$..result..goods_list..sku_group_price[0]"),
    #           "sku_group_price2": jsonpath(firstPageJson, "$..result..goods_list..sku_group_price[1]"),
    #           "sku_price1": jsonpath(firstPageJson, "$..result..goods_list..sku_price[0]"),
    #           "sku_price2": jsonpath(firstPageJson, "$..result..goods_list..sku_price[1]"),
    #           "origin_sku_group_price1": jsonpath(firstPageJson, "$..result..goods_list..origin_sku_group_price[0]"),
    #           "origin_sku_group_price2": jsonpath(firstPageJson, "$..result..goods_list..origin_sku_group_price[1]"),
    #           "promotion_goods": jsonpath(firstPageJson, "$..result..goods_list..promotion_goods"),
    #           "mall_id": jsonpath(firstPageJson, "$..result..goods_list..mall_id"),
    #           "mall_name": jsonpath(firstPageJson, "$..result..goods_list..mall_name"),
    #           "id": jsonpath(firstPageJson, "$..result..goods_list..id"),
    #           "goods_name": jsonpath(firstPageJson, "$..result..goods_list..goods_name"),
    #           "goods_sn": jsonpath(firstPageJson, "$..result..goods_list..goods_sn"),
    #           "goods_type": jsonpath(firstPageJson, "$..result..goods_list..goods_type"),
    #           "cat_id": jsonpath(firstPageJson, "$..result..goods_list..cat_id"),
    #           "cat_name": jsonpath(firstPageJson, "$..result..goods_list..cat_name"),
    #           "event_type": jsonpath(firstPageJson, "$..result..goods_list..event_type"),
    #           "display_priority": jsonpath(firstPageJson, "$..result..goods_list..display_priority"),
    #           "reserve_quantity": jsonpath(firstPageJson, "$..result..goods_list..reserve_quantity"),
    #           "hd_thumb_url": jsonpath(firstPageJson, "$..result..goods_list..hd_thumb_url"),
    #           "image_url": jsonpath(firstPageJson, "$..result..goods_list..image_url"),
    #           "thumb_url": jsonpath(firstPageJson, "$..result..goods_list..thumb_url"),
    #           "is_onsale": jsonpath(firstPageJson, "$..result..goods_list..is_onsale"),
    #           "sold_quantity": jsonpath(firstPageJson, "$..result..goods_list..sold_quantity"),
    #           "off_sale_check": jsonpath(firstPageJson, "$..result..goods_list..off_sale_check"),
    #           "onsale_check": jsonpath(firstPageJson, "$..result..goods_list..onsale_check"),
    #           "edit_check": jsonpath(firstPageJson, "$..result..goods_list..edit_check"),
    #           "gray_goods_high_quality_refund": jsonpath(firstPageJson,
    #                                                      "$..result..goods_list..gray_goods_high_quality_refund"),
    #           "share_desc": jsonpath(firstPageJson, "$..result..goods_list..share_desc"),
    #           "goods_desc": jsonpath(firstPageJson, "$..result..goods_list..goods_desc"),
    #           "activity_title": jsonpath(firstPageJson, "$..result..goods_list..activity_title"),
    #           "is_card_secret": jsonpath(firstPageJson, "$..result..goods_list..is_card_secret"),
    #           "market_price": jsonpath(firstPageJson, "$..result..goods_list..market_price"),
    #           "out_goods_sn": jsonpath(firstPageJson, "$..result..goods_list..out_goods_sn"),
    #           "sold_quantity_for_thirty_days": jsonpath(firstPageJson,
    #                                                     "$..result..goods_list..sold_quantity_for_thirty_days"),
    #           "fav_cnt": jsonpath(firstPageJson, "$..result..goods_list..fav_cnt"),
    #           "multi_treasure": jsonpath(firstPageJson, "$..result..goods_list..multi_treasure"),
    #           "multi_treasure_status": jsonpath(firstPageJson, "$..result..goods_list..multi_treasure_status"),
    #           "if_new_goods": jsonpath(firstPageJson, "$..result..goods_list..if_new_goods"),
    #           "tag_category_list": jsonpath(firstPageJson, "$..result..goods_list..tag_category_list[0]"),
    #           "goods_info_score_dto": jsonpath(firstPageJson, "$..result..goods_list..goods_info_score_dto"),
    #           "goods_inspect_score_score": jsonpath(firstPageJson, "$..result..goods_list..goods_inspect_score..score"),
    #           "goods_inspect_score_problemsDesc": jsonpath(firstPageJson,
    #                                                        "$..result..goods_list..goods_inspect_score..problemsDesc"),
    #           "goods_inspect_score_problemsCode": jsonpath(firstPageJson,
    #                                                        "$..result..goods_list..goods_inspect_score..problemsCode"),
    #           "goods_inspect_score_status": jsonpath(firstPageJson,
    #                                                  "$..result..goods_list..goods_inspect_score..status"),
    #           "goods_inspect_score_taskId": jsonpath(firstPageJson,
    #                                                  "$..result..goods_list..goods_inspect_score..taskId"),
    #           "created_at": jsonpath(firstPageJson, "$..result..goods_list..created_at"),
    #           "pre_sale_time": jsonpath(firstPageJson, "$..result..goods_list..pre_sale_time"),
    #           "shipment_limit_second": jsonpath(firstPageJson, "$..result..goods_list..shipment_limit_second"),
    #           "is_group_pre_sale": jsonpath(firstPageJson, "$..result..goods_list..is_group_pre_sale"),
    #           "guide_target": jsonpath(firstPageJson, "$..result..goods_list..guide_target"),
    #           "over_sell": jsonpath(firstPageJson, "$..result..goods_list..over_sell"),
    #           "market_labels": jsonpath(firstPageJson, "$..result..goods_list..market_labels"),
    #           "labels": jsonpath(firstPageJson, "$..result..goods_list..labels[0]"),
    #           "sku_type": jsonpath(firstPageJson, "$..result..goods_list..sku_type"),
    #           "sku_count": jsonpath(firstPageJson, "$..result..goods_list..sku_count"),
    #           "sku_list": jsonpath(firstPageJson, "$..result..goods_list..sku_list"),
    #           "property_list": jsonpath(firstPageJson, "$..result..goods_list..property_list"),
    #           "schedule_sale_sale_type": jsonpath(firstPageJson, "$..result..goods_list..sale_type"),
    #           "schedule_sale_sale_time_fixed": jsonpath(firstPageJson, "$..result..goods_list..sale_time_fixed"),
    #           "schedule_sale_sale_time": jsonpath(firstPageJson, "$..result..goods_list..sale_time"),
    #           "schedule_sale_price_show": jsonpath(firstPageJson, "$..result..goods_list..price_show"),
    #           "schedule_sale_price_desc": jsonpath(firstPageJson, "$..result..goods_list..price_desc"),
    #           "reject_status": jsonpath(firstPageJson, "$..result..goods_list..reject_status"),
    #           "reject_reason": jsonpath(firstPageJson, "$..result..goods_list..reject_reason")
    #           }
    # iscore = len(jsonpath(firstPageJson, "$..result..goods_list..score"))
    # iquantity = len(jsonpath(firstPageJson, "$..result..goods_list..quantity"))
    # isku_group_price0 = len(jsonpath(firstPageJson, "$..result..goods_list..sku_group_price[0]"))
    # isku_group_price1 = len(jsonpath(firstPageJson, "$..result..goods_list..sku_group_price[1]"))
    # isku_price0 = len(jsonpath(firstPageJson, "$..result..goods_list..sku_price[0]"))
    # isku_price1 = len(jsonpath(firstPageJson, "$..result..goods_list..sku_price[1]"))
    # iorigin_sku_group_price0 = len(jsonpath(firstPageJson, "$..result..goods_list..origin_sku_group_price[0]"))
    # iorigin_sku_group_price1 = len(jsonpath(firstPageJson, "$..result..goods_list..origin_sku_group_price[1]"))
    # iid = len(jsonpath(firstPageJson, "$..result..goods_list..id"))
    # igoods_name = len(jsonpath(firstPageJson, "$..result..goods_list..goods_name"))
    # igoods_sn = len(jsonpath(firstPageJson, "$..result..goods_list..goods_sn"))
    # icat_id = len(jsonpath(firstPageJson, "$..result..goods_list..cat_id"))
    # icat_name = len(jsonpath(firstPageJson, "$..result..goods_list..cat_name"))
    # ihd_thumb_url = len(jsonpath(firstPageJson, "$..result..goods_list..hd_thumb_url"))
    # iimage_url = len(jsonpath(firstPageJson, "$..result..goods_list..image_url"))
    # ithumb_url = len(jsonpath(firstPageJson, "$..result..goods_list..thumb_url"))
    # iis_onsale = len(jsonpath(firstPageJson, "$..result..goods_list..is_onsale"))
    # isold_quantity = len(jsonpath(firstPageJson, "$..result..goods_list..sold_quantity"))
    # imarket_price = len(jsonpath(firstPageJson, "$..result..goods_list..market_price"))
    # ifav_cnt = len(jsonpath(firstPageJson, "$..result..goods_list..fav_cnt"))
    # iif_new_goods = len(jsonpath(firstPageJson, "$..result..goods_list..if_new_goods"))
    # igoods_info_score_dto = len(jsonpath(firstPageJson, "$..result..goods_list..goods_info_score_dto"))
    # igoods_inspect_score_score = len(jsonpath(firstPageJson, "$..result..goods_list..goods_inspect_score..score"))
    # icreated_at = len(jsonpath(firstPageJson, "$..result..goods_list..created_at"))
    # isold_quantity_for_thirty_days = len(
    #     jsonpath(firstPageJson, "$..result..goods_list..sold_quantity_for_thirty_days"))
    #
    # lenthArray = [iscore,
    #               iquantity,
    #               isku_group_price0,
    #               isku_group_price1,
    #               isku_price0,
    #               isku_price1,
    #               iorigin_sku_group_price0,
    #               iorigin_sku_group_price1,
    #               iid,
    #               igoods_name,
    #               igoods_sn,
    #               icat_id,
    #               icat_name,
    #               ihd_thumb_url,
    #               iimage_url,
    #               ithumb_url,
    #               iis_onsale,
    #               isold_quantity,
    #               imarket_price,
    #               ifav_cnt,
    #               iif_new_goods,
    #               igoods_info_score_dto,
    #               igoods_inspect_score_score,
    #               icreated_at,
    #               isold_quantity_for_thirty_days]
    # averageNumber = np.mean(lenthArray)
    # npArray = np.array(lenthArray)
    # missArray = npArray[npArray < averageNumber]

    # aliJson = firstPageJson['result']['goods_list']
    # for l in missArray:
    #     x = np.where(lenthArray == l)
    #     for m in x[0]:
    #         if m == 0:
    #             for n in range(0, len(aliJson)):
    #                 if 'score' not in aliJson[n]:
    #                     aliJson[n]['score'] = ''
    #         elif m == 1:
    #             for n in range(0, len(aliJson)):
    #                 if 'quantity' not in aliJson[n]:
    #                     aliJson[n]['quantity'] = ''

    for i in range(0, len(firstPageJson['result']['goods_list'])):
        firstPageJson['result']['goods_list'][i]['scoreCopy'] = firstPageJson['result']['goods_list'][i]['score']

    jsonDf = {
        "score": jsonpath(firstPageJson, "$..result..goods_list..scoreCopy"),
        "quantity": jsonpath(firstPageJson, "$..result..goods_list..quantity"),
        "sku_group_price1": jsonpath(firstPageJson, "$..result..goods_list..sku_group_price[0]"),
        "sku_group_price2": jsonpath(firstPageJson, "$..result..goods_list..sku_group_price[1]"),
        "sku_price1": jsonpath(firstPageJson, "$..result..goods_list..sku_price[0]"),
        "sku_price2": jsonpath(firstPageJson, "$..result..goods_list..sku_price[1]"),
        "origin_sku_group_price1": jsonpath(firstPageJson, "$..result..goods_list..origin_sku_group_price[0]"),
        "origin_sku_group_price2": jsonpath(firstPageJson, "$..result..goods_list..origin_sku_group_price[1]"),
        "goods_id": jsonpath(firstPageJson, "$..result..goods_list..id"),
        "goods_name": jsonpath(firstPageJson, "$..result..goods_list..goods_name"),
        "goods_sn": jsonpath(firstPageJson, "$..result..goods_list..goods_sn"),
        "cat_id": jsonpath(firstPageJson, "$..result..goods_list..cat_id"),
        "cat_name": jsonpath(firstPageJson, "$..result..goods_list..cat_name"),
        "hd_thumb_url": jsonpath(firstPageJson, "$..result..goods_list..hd_thumb_url"),
        "image_url": jsonpath(firstPageJson, "$..result..goods_list..image_url"),
        "thumb_url": jsonpath(firstPageJson, "$..result..goods_list..thumb_url"),
        "is_onsale": jsonpath(firstPageJson, "$..result..goods_list..is_onsale"),
        "sold_quantity": jsonpath(firstPageJson, "$..result..goods_list..sold_quantity"),
        "market_price": jsonpath(firstPageJson, "$..result..goods_list..market_price"),
        "fav_cnt": jsonpath(firstPageJson, "$..result..goods_list..fav_cnt"),
        "if_new_goods": jsonpath(firstPageJson, "$..result..goods_list..if_new_goods"),
        "goods_info_score_dto": jsonpath(firstPageJson, "$..result..goods_list..goods_info_score_dto"),
        "goods_inspect_score_score": jsonpath(firstPageJson, "$..result..goods_list..goods_inspect_score..score"),
        "created_at": jsonpath(firstPageJson, "$..result..goods_list..created_at"),
        "sold_quantity_for_thirty_days": jsonpath(firstPageJson,
                                                  "$..result..goods_list..sold_quantity_for_thirty_days")
    }
    df = pd.read_json(json.dumps(jsonDf))
    df['statDate'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
    df['shopName'] = jsonFromWebShopName['result']['username']
    df['插入时间'] = datetime.datetime.now()
    df['运行模式'] = runMode
    df['统计月'] = ''
    df['统计周'] = ''
    df['统计日'] = ''
    df['统计日周月'] = ''
    if runMode == '月模式':
        df['统计月'] = str(datetime.datetime.now().month - 1) + "月"
        df['统计日周月'] = df['统计月']
    if runMode == '周模式':
        df['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,
                                      datetime.datetime.now().day)
        df['统计日周月'] = df['统计周']
    if runMode == '日模式':
        df['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
        df['统计日周月'] = df['统计日']

    df['uuid'] = globalUUID
    # 解决可能出现的超时问题bugfix 2020.08.18
    conn.connection.connection.ping(reconnect=True)
    df.to_sql(name=modeName, con=conn, if_exists='append', index=False)

    return jsonFromWebShopName['result']['username'], firstPageJson['result']['total']


def LoopNextPage(shopName, engine, conn, totalRows):
    # 循环点击下一页，来遍历所有的商品
    for i in range(1, math.ceil(totalRows / 100)):
        # 清空network
        pyautogui.moveTo(1541, 144, 4)
        pyautogui.click()

        # 移动到空白区域
        pyautogui.moveTo(1357, 670, 4)
        pyautogui.scroll(-50000)

        # 点击下一页
        pyautogui.moveTo(1370, 570, 4)
        pyautogui.click()

        # 等下，否则100条出不来
        sleep(6)

        # 选择文件
        pyautogui.moveTo(1530, 324, 4)
        pyautogui.click()

        # 点击json区域
        pyautogui.moveTo(1840, 434, 2)
        pyautogui.click()

        # 过滤框textbox中，总是：【goodsList】，我们就不用去换了
        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # 读取剪切板内容json内容
        sleep(2)
        value = clip.paste()
        LoopPageJson = json.loads(value)

        # iscore = len(jsonpath(LoopPageJson, "$..result..goods_list..score"))
        # iquantity = len(jsonpath(LoopPageJson, "$..result..goods_list..quantity"))
        # isku_group_price0 = len(jsonpath(LoopPageJson, "$..result..goods_list..sku_group_price[0]"))
        # isku_group_price1 = len(jsonpath(LoopPageJson, "$..result..goods_list..sku_group_price[1]"))
        # isku_price0 = len(jsonpath(LoopPageJson, "$..result..goods_list..sku_price[0]"))
        # isku_price1 = len(jsonpath(LoopPageJson, "$..result..goods_list..sku_price[1]"))
        # iorigin_sku_group_price0 = len(jsonpath(LoopPageJson, "$..result..goods_list..origin_sku_group_price[0]"))
        # iorigin_sku_group_price1 = len(jsonpath(LoopPageJson, "$..result..goods_list..origin_sku_group_price[1]"))
        # iid = len(jsonpath(LoopPageJson, "$..result..goods_list..id"))
        # igoods_name = len(jsonpath(LoopPageJson, "$..result..goods_list..goods_name"))
        # igoods_sn = len(jsonpath(LoopPageJson, "$..result..goods_list..goods_sn"))
        # icat_id = len(jsonpath(LoopPageJson, "$..result..goods_list..cat_id"))
        # icat_name = len(jsonpath(LoopPageJson, "$..result..goods_list..cat_name"))
        # ihd_thumb_url = len(jsonpath(LoopPageJson, "$..result..goods_list..hd_thumb_url"))
        # iimage_url = len(jsonpath(LoopPageJson, "$..result..goods_list..image_url"))
        # ithumb_url = len(jsonpath(LoopPageJson, "$..result..goods_list..thumb_url"))
        # iis_onsale = len(jsonpath(LoopPageJson, "$..result..goods_list..is_onsale"))
        # isold_quantity = len(jsonpath(LoopPageJson, "$..result..goods_list..sold_quantity"))
        # imarket_price = len(jsonpath(LoopPageJson, "$..result..goods_list..market_price"))
        # ifav_cnt = len(jsonpath(LoopPageJson, "$..result..goods_list..fav_cnt"))
        # iif_new_goods = len(jsonpath(LoopPageJson, "$..result..goods_list..if_new_goods"))
        # igoods_info_score_dto = len(jsonpath(LoopPageJson, "$..result..goods_list..goods_info_score_dto"))
        # igoods_inspect_score_score = len(jsonpath(LoopPageJson, "$..result..goods_list..goods_inspect_score..score"))
        # icreated_at = len(jsonpath(LoopPageJson, "$..result..goods_list..created_at"))
        # isold_quantity_for_thirty_days = len(
        #     jsonpath(LoopPageJson, "$..result..goods_list..sold_quantity_for_thirty_days"))
        #
        # lenthArray = [iscore,
        #               iquantity,
        #               isku_group_price0,
        #               isku_group_price1,
        #               isku_price0,
        #               isku_price1,
        #               iorigin_sku_group_price0,
        #               iorigin_sku_group_price1,
        #               iid,
        #               igoods_name,
        #               igoods_sn,
        #               icat_id,
        #               icat_name,
        #               ihd_thumb_url,
        #               iimage_url,
        #               ithumb_url,
        #               iis_onsale,
        #               isold_quantity,
        #               imarket_price,
        #               ifav_cnt,
        #               iif_new_goods,
        #               igoods_info_score_dto,
        #               igoods_inspect_score_score,
        #               icreated_at,
        #               isold_quantity_for_thirty_days]

        for xx in range(0, len(LoopPageJson['result']['goods_list'])):
            LoopPageJson['result']['goods_list'][xx]['scoreCopy'] = LoopPageJson['result']['goods_list'][xx]['score']

        jsonDf = {
            "score": jsonpath(LoopPageJson, "$..result..goods_list..scoreCopy"),
            "quantity": jsonpath(LoopPageJson, "$..result..goods_list..quantity"),
            "sku_group_price1": jsonpath(LoopPageJson, "$..result..goods_list..sku_group_price[0]"),
            "sku_group_price2": jsonpath(LoopPageJson, "$..result..goods_list..sku_group_price[1]"),
            "sku_price1": jsonpath(LoopPageJson, "$..result..goods_list..sku_price[0]"),
            "sku_price2": jsonpath(LoopPageJson, "$..result..goods_list..sku_price[1]"),
            "origin_sku_group_price1": jsonpath(LoopPageJson, "$..result..goods_list..origin_sku_group_price[0]"),
            "origin_sku_group_price2": jsonpath(LoopPageJson, "$..result..goods_list..origin_sku_group_price[1]"),
            "goods_id": jsonpath(LoopPageJson, "$..result..goods_list..id"),
            "goods_name": jsonpath(LoopPageJson, "$..result..goods_list..goods_name"),
            "goods_sn": jsonpath(LoopPageJson, "$..result..goods_list..goods_sn"),
            "cat_id": jsonpath(LoopPageJson, "$..result..goods_list..cat_id"),
            "cat_name": jsonpath(LoopPageJson, "$..result..goods_list..cat_name"),
            "hd_thumb_url": jsonpath(LoopPageJson, "$..result..goods_list..hd_thumb_url"),
            "image_url": jsonpath(LoopPageJson, "$..result..goods_list..image_url"),
            "thumb_url": jsonpath(LoopPageJson, "$..result..goods_list..thumb_url"),
            "is_onsale": jsonpath(LoopPageJson, "$..result..goods_list..is_onsale"),
            "sold_quantity": jsonpath(LoopPageJson, "$..result..goods_list..sold_quantity"),
            "market_price": jsonpath(LoopPageJson, "$..result..goods_list..market_price"),
            "fav_cnt": jsonpath(LoopPageJson, "$..result..goods_list..fav_cnt"),
            "if_new_goods": jsonpath(LoopPageJson, "$..result..goods_list..if_new_goods"),
            "goods_info_score_dto": jsonpath(LoopPageJson, "$..result..goods_list..goods_info_score_dto"),
            "goods_inspect_score_score": jsonpath(LoopPageJson,
                                                  "$..result..goods_list..goods_inspect_score..score"),
            "created_at": jsonpath(LoopPageJson, "$..result..goods_list..created_at"),
            "sold_quantity_for_thirty_days": jsonpath(LoopPageJson,
                                                      "$..result..goods_list..sold_quantity_for_thirty_days")
        }
        dfLoop = pd.read_json(json.dumps(jsonDf))
        dfLoop['statDate'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
        dfLoop['shopName'] = shopName
        dfLoop['插入时间'] = datetime.datetime.now()
        dfLoop['运行模式'] = runMode
        dfLoop['统计日周月'] = ''
        if runMode == '月模式':
            dfLoop['统计月'] = str(datetime.datetime.now().month - 1) + "月"
            dfLoop['统计日周月'] = dfLoop['统计月']
        if runMode == '周模式':
            dfLoop['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,
                                              datetime.datetime.now().day)
            dfLoop['统计日周月'] = dfLoop['统计周']
        if runMode == '日模式':
            dfLoop['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
                "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
            dfLoop['统计日周月'] = dfLoop['统计日']
        dfLoop['uuid'] = globalUUID
        # 解决可能出现的超时问题bugfix 2020.08.18
        conn.connection.connection.ping(reconnect=True)
        dfLoop.to_sql(name=modeName, con=conn, if_exists='append', index=False)

    resetGuiAtLastStepCloseF12ToTop()

    return


def lambdaGetWeek(statDateStr):
    return getWeekOfYear(int(statDateStr.split("-")[0]), int(statDateStr.split("-")[1]), int(statDateStr.split("-")[2]))


def lambdaGetMonth(statDateStr):
    return str(int(statDateStr.split("-")[1])) + "月"


def deleteDuplicationData1(engine):
    # 这里删除数据，要纳入uuid的考虑，比较难做到完全不重复，起码有uuid，是可以区分开的
    # 拼多多_商家后台_商品列表_线上商品
    sql = 'delete from a                                                              '
    sql += '    using ' + modeName + ' as a, ' + modeName + ' as b   '
    sql += '    where (a.id < b.id)                                                    '
    sql += '    and (a.uuid = b.uuid and a.goods_id=b.goods_id and a.statDate=b.statDate )     '
    engine.execute(sql)


def deleteDuplicationData2(engine):
    # 这里删除数据，要纳入uuid的考虑，比较难做到完全不重复，起码有uuid，是可以区分开的
    # 拼多多_商家后台_商品列表_线上商品
    sql = 'delete from a                                                              '
    sql += '    using ' + modeName + ' as a, ' + modeName + ' as b   '
    sql += '    where (a.id < b.id)                                                    '
    sql += '    and (a.goods_id=b.goods_id and a.statDate=b.statDate )     '
    engine.execute(sql)


def executeCatchWeb(engine, conn):
    try:
        LogTaskAndMachine(modeName, engine, conn, '', runMode)
        shopName, totalRows = SPGK(engine, conn)
        LoopNextPage(shopName, engine, conn, totalRows)
        # 这里删除数据，要纳入uuid的考虑，比较难做到完全不重复，起码有uuid，是可以区分开的
        deleteDuplicationData1(engine)
        deleteDuplicationData2(engine)
    except Exception as e:
        traceback.print_exc()
        sendAlert('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1], engine, conn)
        print('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1])
        return
    sendFinalSuccessEmail('●正常完成●' + modeName, '', engine, conn, modeName, runMode)


if __name__ == '__main__':
    # 连接database
    engine, conn = getConn()
    executeCatchWeb(engine, conn)
